package com.oracletomysql;

import com.alibaba.fastjson.JSON;
import com.oracletomysql.pojo.ArvNoRuleJsonVO;
import com.oracletomysql.pojo.BaseArvType;
import com.oracletomysql.pojo.BaseArvTypeFileType;
import com.oracletomysql.pojo.BaseFileType;
import com.oracletomysql.utils.DateUtil;
import com.oracletomysql.utils.MysqlGetConnection;
import com.oracletomysql.utils.OracleGetConnection;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

/**
 * @author 薛虎
 * @date 2021/1/20 13:31
 * 数字化加工全宗数据迁移
 * 退休档案
 * 档案科
 * 企业档案
 */
public class OracleToMysqlFL {


    public static void main(String[] args) {

        System.out.println("请输入档案分类名称:");
        Scanner scanner = new Scanner(System.in);

        Connection connection = null;
        try {
            String sql = "select * from BAS_ARVTYPE where ARVTYPENAM = " + "\'" + scanner.nextLine() + "\'";
            QueryRunner runner = new QueryRunner();
            MapListHandler handler = new MapListHandler();
            connection = OracleGetConnection.getOracleConnection();
            List<Map<String, Object>> list = runner.query(connection, sql, handler);

            System.out.println(list.get(0));
            inseartBaseArvType(list);

            String sql2 = "select * from BAS_ARVTYPE_FILETYPE where ARVTYPEID = " + "\'" + list.get(0).get("ARVTYPEID") + "\'";

            List<Map<String, Object>> query = runner.query(connection, sql2, handler);
            insetFileTypeAndArv(query);
//
//
            for (Map<String, Object> map : query) {
                String sqlss = "select * from BAS_FILETYPE where FILETYPEID = " + "\'" + map.get("FILETYPEID") + "\'";
                //查询单个对象
                Map<String, Object> query1 = runner.query(connection, sqlss, new MapHandler());

                insertFile(query1);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (!connection.isClosed()) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

    //插入档案分类
    public static void inseartBaseArvType(List<Map<String, Object>> list) {


        BaseArvType baseArvType = new BaseArvType();
        //档案分类ID
        baseArvType.setArvTypeId((String) list.get(0).get("ARVTYPEID"));
        //档案分类编码
        baseArvType.setArvTypeCode((String) list.get(0).get("ARVTYPECOD"));
        //档案分类名称
        baseArvType.setArvTypeName((String) list.get(0).get("ARVTYPENAM"));
        //父节点ID
        baseArvType.setPid("0");
        //描述
        baseArvType.setDescr((String) list.get(0).get("DESCR"));
        //密级
        baseArvType.setSecLevel(1);
        //保密期限
        baseArvType.setKeepTerm(100);
        //全宗id
        baseArvType.setGnlArvId((String) list.get(0).get("GNLARVID"));
        //序号
        baseArvType.setSeqNo(0);


        StringBuilder stringBuilder = new StringBuilder();

        //档案分类编码
        stringBuilder.append((String) list.get(0).get("ARVTYPECOD")).append("-");

        //设置立档时间
        Calendar cal = Calendar.getInstance();
        String year = String.valueOf(cal.get(Calendar.YEAR));
        stringBuilder.append(year).append("-");

        ArvNoRuleJsonVO arvNoRuleJsonVO = new ArvNoRuleJsonVO();
        arvNoRuleJsonVO.setRule(stringBuilder.deleteCharAt(stringBuilder.length() - 1).toString());
        arvNoRuleJsonVO.setSpliteArvNo("-");

        String jsonString = JSON.toJSONString(arvNoRuleJsonVO);

        //档号规则
        baseArvType.setJsonRuleArvNo(jsonString);
        //档案类别
        baseArvType.setArvCat("res");
        //是否审核1:审核 0:不审核
        baseArvType.setNeedAudit(1);
        //创建时间
        long nowSecond = DateUtil.nowSecond();
        baseArvType.setCreateTime(nowSecond);
        //更新时间
        baseArvType.setUpdateTime(nowSecond);

        Connection connection = null;
        try {


            QueryRunner runner = new QueryRunner();
            connection = MysqlGetConnection.getMysqlConnection();
            connection.setAutoCommit(false);
            String sql = "INSERT INTO base_arv_type" +
                    "(arv_type_id,arv_type_code,arv_type_name,pid,descr, sec_level, keep_term, gnl_arv_id,seq_no,json_rule_arv_no," +
                    "json_rule_vol_no,arv_cat,need_audit,create_time,update_time) " +
                    "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            runner.update(connection, sql, baseArvType.getArvTypeId(), baseArvType.getArvTypeCode(), baseArvType.getArvTypeName(), baseArvType.getPid(),
                    baseArvType.getDescr(), baseArvType.getSecLevel(), baseArvType.getKeepTerm(), baseArvType.getGnlArvId(), baseArvType.getSeqNo(), baseArvType.getJsonRuleArvNo(),
                    baseArvType.getJsonRuleVolNo(), baseArvType.getArvCat(), baseArvType.getNeedAudit(), baseArvType.getCreateTime(), baseArvType.getUpdateTime());
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        } finally {
            try {
                if (!connection.isClosed()) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

    //关联信息表插入数据
    public static void insetFileTypeAndArv(List<Map<String, Object>> list) {

        Connection connection = null;

        try {
            for (Map<String, Object> map : list) {
                connection = MysqlGetConnection.getMysqlConnection();
                connection.setAutoCommit(false);
                QueryRunner queryRunner = new QueryRunner();

                String sql = "INSERT INTO base_arv_type_file_type(arv_type_file_type_pk,arv_type_id,file_type_id," +
                        "seq_no,pid,is_required,status,hidden_status,descr,create_time,update_time)" +
                        " values(?,?,?,?,?,?,?,?,?,?,?)";


                BaseArvTypeFileType baseArvTypeFileType = new BaseArvTypeFileType();
                //主键
                baseArvTypeFileType.setArvTypeFileTypePk(map.get("PK").toString());
                //档案分类id
                baseArvTypeFileType.setArvTypeId(map.get("ARVTYPEID").toString());
                //文件分类ID
                baseArvTypeFileType.setFileTypeId(map.get("FILETYPEID").toString());
                //序号
                baseArvTypeFileType.setSeqNo(0);
                //父id
                baseArvTypeFileType.setPid("0");
                //是否必须
                baseArvTypeFileType.setIsRequired(0);
                //状态正常1；逻辑删除2
                baseArvTypeFileType.setStatus(1);
                //是否隐藏
                baseArvTypeFileType.setHiddenStatus(1);
                //备注
                baseArvTypeFileType.setDescr("数字化加工市直二期迁移数据");
                long nowSecond = DateUtil.nowSecond();
                baseArvTypeFileType.setCreateTime(nowSecond);
                baseArvTypeFileType.setUpdateTime(nowSecond);


                queryRunner.update(connection, sql, baseArvTypeFileType.getArvTypeFileTypePk(), baseArvTypeFileType.getArvTypeId(), baseArvTypeFileType.getFileTypeId(),
                        baseArvTypeFileType.getSeqNo(), baseArvTypeFileType.getPid(), baseArvTypeFileType.getIsRequired(), baseArvTypeFileType.getStatus(), baseArvTypeFileType.getHiddenStatus(),
                        baseArvTypeFileType.getDescr(), baseArvTypeFileType.getCreateTime(), baseArvTypeFileType.getUpdateTime());
                connection.commit();
            }
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

            e.printStackTrace();
        } finally {
            try {
                if (!connection.isClosed()) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

    //插入材料清单的数据
    public static void insertFile(Map<String, Object> map) {

        Connection connection = null;
        try {
            connection = MysqlGetConnection.getMysqlConnection();
            String sql = "INSERT INTO base_file_type(file_type_id, file_type_code,file_type_name,file_format,create_time,update_time)" +
                    " values(?,?,?,?,?,?)";
            connection.setAutoCommit(false);
            BaseFileType baseFileType = new BaseFileType();
            //文件ID
            baseFileType.setFileTypeId(map.get("FILETYPEID").toString());

            //文件类型编码
            baseFileType.setFileTypeCode(map.get("FILETYPECOD").toString());

            //文件类型名称
            baseFileType.setFileTypeName(map.get("FILETYPENAM").toString());

            //文件类型
            baseFileType.setFileFormat(map.get("FILEFORMAT").toString());

            long nowSecond = DateUtil.nowSecond();
            baseFileType.setUpdateTime(nowSecond);
            baseFileType.setCreateTime(nowSecond);


            QueryRunner queryRunner = new QueryRunner();

            queryRunner.update(connection, sql, baseFileType.getFileTypeId(), baseFileType.getFileTypeCode(), baseFileType.getFileTypeName(), baseFileType.getFileFormat(),
                    baseFileType.getUpdateTime(), baseFileType.getCreateTime());
            connection.commit();

        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (!connection.isClosed()) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
